﻿using System;
using Dapper;
using System.Linq;
using System.Text;
using SilenthinkContract.Domain;

namespace SilenthinkService
{
    internal static partial class SqlTemplate
    {
        internal static class StoreTemplate
        {
            internal static SqlBuilder.Template CreateProductCategory(ProductCategory o)
            {
                var builder = new SqlBuilder();
                builder.Table(DatabaseSchema.StoreSchema.Table.ProductCategory)
                    .Field("(Name)VALUES(@Name)");
                builder.AddParameters(new { o.Name });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template GetProductCategory(int? id = null, bool? status = null)
            {
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(DatabaseSchema.StoreSchema.Table.ProductCategory);

                if (id.HasValue)
                {
                    builder.Where("[Id]=@Id", new { id = id.Value });
                }
                if (status.HasValue)
                {
                    builder.Where("[Status]=@Status", new { status = status.Value });
                }
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            internal static SqlBuilder.Template CreateProduct(Product o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.Product)
                    .Field("([Name],[CategoryCode])VALUES(@Name,@CategoryCode)")
                    .AddParameters(new { o.Name, o.CategoryCode });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template UpdateProduct(Product o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.Product)
                    .Field("[Name]=@Name", new { o.Name })
                    .Where("[Id]=@Id AND [Version]=@Version", new { o.Id, o.Version });

                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template DeleteProduct(long id, bool physical = false)
            {
                if (physical)
                {
                    return new SqlBuilder().Table(DatabaseSchema.StoreSchema.Table.Product)
                        .Where("[Id]=@Id", new { id }).AddTemplate(BasicSingleTableSqlTemplate.Delete);
                }
                return new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.Product)
                    .Field("[Status]=@Status", new { Status = ProductStatus.Deleted })
                    .Where("[Id]=@Id", new { id }).AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template CreateProductExtra(ProductExtra o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.ProductExtra)
                    .Field("([Id],[SeName])VALUES(@Id,@SeName)")
                    .AddParameters(new { o.Id, o.SeName });

                var template = builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
                return template;
            }

            internal static SqlBuilder.Template GetProductExtra(long id)
            {
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(DatabaseSchema.StoreSchema.Table.ProductExtra)
                    .Where("[Id]=@Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            internal static SqlBuilder.Template CreateProductAttribute(ProductAttribute[] o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.ProductAttribute)
                    .Field(
                        "([ProductId],[ProductCategoryCode],[AttributeNameCode],[AttributeValueCode],[CreatedBy],[CreatedOn])VALUES(@ProductId,@ProductCategoryCode,@AttributeNameCode,@AttributeValueCode,@CreatedBy,@CreatedOn)")
                    ;
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template CreateProductVariant(ProductVariant[] o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.ProductVariant)
                    .Field(
                        "([ProductId],[VariantCode],[VariantPrice],[VariantQuantity],[CreatedBy],[CreatedOn])VALUES(@ProductId,@VariantCode,@VariantPrice,@VariantQuantity,@CreatedBy,@CreatedOn)");
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template GetProduct(long id, bool inclExtra, bool inclVariants)
            {
                var sb = new StringBuilder();
                sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;", DatabaseSchema.StoreSchema.Table.Product);
                if (inclExtra)
                {
                    sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;",
                                    DatabaseSchema.StoreSchema.Table.ProductExtra);
                }
                if (inclVariants)
                {
                    sb.AppendFormat("SELECT * FROM {0} WHERE [ProductId]=@Id;",
                                    DatabaseSchema.StoreSchema.Table.ProductVariant);
                }

                return new SqlBuilder().AddTemplate(sb.ToString(), new { id });
            }

            internal static SqlBuilder.Template GetProductList(int size = 20, int index = 1, string name = "")
            {
                if (index < 1)
                {
                    index = 1;
                }
                if (size < 0 || size > 200)
                {
                    size = 20;
                }

                var builder = new SqlBuilder().Field("*")
                    .Table(DatabaseSchema.StoreSchema.Table.Product)
                    .OrderBy("id desc").AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

                if (!string.IsNullOrEmpty(name))
                {
                    builder.Where("[Name]=@Name", new { name });
                }

                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }

            internal static SqlBuilder.Template GetProductAttributeName(string productCategoryCode,
                                                                        string parentAttributeNameCode, int? id = null,
                                                                        bool? status = null)
            {
                var builder = new SqlBuilder().Field("*")
                    .Table(DatabaseSchema.StoreSchema.Table.ProductAttributeName)
                    .Where("[ParentAttributeNameCode]=@parentAttributeNameCode", new { parentAttributeNameCode });

                if (!string.IsNullOrEmpty(productCategoryCode))
                {
                    if (productCategoryCode.Length > 2 && productCategoryCode.Length % 2 == 0)
                    {
                        var x = productCategoryCode.Length / 2;
                        var y = new string[x];
                        var length = 2;
                        for (var i = 1; i <= x; i++)
                        {
                            var temp = productCategoryCode.Substring(0, length);
                            y[i - 1] = temp;
                            length = (i + 1) * 2;
                        }
                        var str = y.Aggregate(string.Empty, (current, o) => current + ("'" + o + "',")).Trim(',');

                        builder.Where("[ProductCategoryCode] IN(" + str + ")");
                    }
                    else
                    {
                        builder.Where("[ProductCategoryCode]=@productCategoryCode", new { productCategoryCode });
                    }
                }
                if (id.HasValue)
                {
                    builder.Where("[Id]=@Id", new { id = id.Value });
                }
                if (status.HasValue)
                {
                    builder.Where("[Status]=@Status", new { status = status.Value });
                }
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            internal static SqlBuilder.Template GetProductAttributeValue(string productCategoryCode,
                                                                         bool byAttributeNameCode,
                                                                         string[] attributeNameCodeArray, int? id = null,
                                                                         bool? status = null)
            {
                var builder = new SqlBuilder().Field("*")
                    .Table(DatabaseSchema.StoreSchema.Table.ProductAttributeValue);

                if (productCategoryCode.Length > 2 && productCategoryCode.Length % 2 == 0)
                {
                    var x = productCategoryCode.Length / 2;
                    var y = new string[x];
                    var length = 2;
                    for (var i = 1; i <= x; i++)
                    {
                        var temp = productCategoryCode.Substring(0, length);
                        y[i - 1] = temp;
                        length = (i + 1) * 2;
                    }
                    var str = y.Aggregate(string.Empty, (current, o) => current + ("'" + o + "',")).Trim(',');

                    builder.Where("[ProductCategoryCode] IN(" + str + ")");
                }
                else
                {
                    builder.Where("[ProductCategoryCode]=@productCategoryCode", new { productCategoryCode });
                }

                if (byAttributeNameCode)
                {
                    if (attributeNameCodeArray.Length > 0)
                    {
                        var s = attributeNameCodeArray.Aggregate(string.Empty,
                                                                 (current, o) => current + ("'" + o + "',"));
                        builder.Where(string.Format("[AttributeNameCode] IN ({0})", s.TrimEnd(',')));
                    }
                }
                if (id.HasValue)
                {
                    builder.Where("[Id]=@Id", new { id = id.Value });
                }
                if (status.HasValue)
                {
                    builder.Where("[Status]=@Status", new { status = status.Value });
                }

                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            internal static SqlBuilder.Template CreateProductAttributeName(ProductAttributeName o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.ProductAttributeName).Field(
                        "([AttributeNameCode],[ParentAttributeNameCode],[ParentAttributeValueCode],[AttributeName],[AttributeNameDescription],[AttributeNameLevel],[AttributeType],[AttributeControlType],[IsMust],[ProductCategoryCode],[HasChild],[ChildAttributeNameCode],[DisplayOrder],[Status],[CreatedBy],[CreatedOn])VALUES(@AttributeNameCode,@ParentAttributeNameCode,@ParentAttributeValueCode,@AttributeName,@AttributeNameDescription,@AttributeNameLevel,@AttributeType,@AttributeControlType,@IsMust,@ProductCategoryCode,@HasChild,@ChildAttributeNameCode,@DisplayOrder,@Status,@CreatedBy,@CreatedOn)")
                    .AddParameters(new {o.AttributeNameCode,o.ParentAttributeNameCode,o.ParentAttributeValueCode,o.AttributeName,o.AttributeNameDescription,o.AttributeNameLevel,o.AttributeType,o.AttributeControlType,o.IsMust,o.ProductCategoryCode,o.HasChild,o.ChildAttributeNameCode,o.DisplayOrder,o.Status,o.CreatedBy,o.CreatedOn});
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template CreateProductAttributeValue(ProductAttributeValue o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.ProductAttributeValue)
                    .Field(
                        "([AttributeValueCode],[AttributeValueName],[AttributeValueDescription],[AttributeNameCode],[ProductCategoryCode],[PreSelected],[DisplayOrder],[Status],[CreatedBy],[CreatedOn])VALUES(@AttributeValueCode,@AttributeValueName,@AttributeValueDescription,@AttributeNameCode,@ProductCategoryCode,@PreSelected,@DisplayOrder,@Status,@CreatedBy,@CreatedOn)")
                    .AddParameters(new {o.AttributeValueCode,o.AttributeValueName,o.AttributeValueDescription,o.AttributeNameCode,o.ProductCategoryCode,o.PreSelected,o.DisplayOrder,o.Status,o.CreatedBy,o.CreatedOn});
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template GetProductAttributeNameValue(long productId, string productCategoryCode,bool isSaleAttribute)
            {
                var builder = new SqlBuilder()
                    .Where(isSaleAttribute ? "[AttributeType]=2" : "[AttributeType]<>2")
                    .Where("attribute.ProductId=@ProductId", new { productId });
                if (productCategoryCode.Length > 2 && productCategoryCode.Length % 2 == 0)
                {
                    var x = productCategoryCode.Length / 2;
                    var y = new string[x];
                    var length = 2;
                    for (var i = 1; i <= x; i++)
                    {
                        var temp = productCategoryCode.Substring(0, length);
                        y[i - 1] = temp;
                        length = (i + 1) * 2;
                    }
                    var str = y.Aggregate(string.Empty, (current, o) => current + ("'" + o + "',")).Trim(',');

                    builder.Where("(name.ProductCategoryCode IN(" + str + ") AND value.ProductCategoryCode  IN(" + str + "))");
                }
                else
                {
                    builder.Where(
                        "(name.ProductCategoryCode =@ProductCategoryCode AND value.ProductCategoryCode=@ProductCategoryCode)",
                        new { productCategoryCode });
                }

                return
                    builder.AddTemplate(
                        "select attribute.AttributeNameCode AS NameCode,attribute.AttributeValueCode AS ValueCode,name.AttributeName As Name,value.AttributeValueName AS Value from [ProductAttribute] attribute inner join dbo.ProductAttributeName name on attribute.AttributeNameCode=name.AttributeNameCode inner join  dbo.ProductAttributeValue value on attribute.AttributeValueCode=value.AttributeValueCode /**where**/");
            }

            internal static SqlBuilder.Template UpdateProductXmlAttribute(Product o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.Product)
                    .Field("[XmlAttribute]=@XmlAttribute", new { o.XmlAttribute })
                    .Field("[SaleXmlAttribute]=@SaleXmlAttribute", new { o.SaleXmlAttribute })
                    .Where("[Id]=@Id", new { o.Id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            #region Discount

            internal static SqlBuilder.Template GetDiscount(int id)
            {
                var sb = new StringBuilder();
                sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE ID=@Id;", DatabaseSchema.StoreSchema.Table.Discount);
                sb.AppendFormat("SELECT * FROM {0} WHERE DiscountId=@Id;",
                                DatabaseSchema.StoreSchema.Table.DiscountRequirement);
                var template = new SqlBuilder().AddTemplate(sb.ToString(), new { id });
                return template;
            }


            internal static SqlBuilder.Template GetDiscount(int size = 20, int index = 1,
                                                            DiscountAssignedTo? assignedTo = null,
                                                            bool? usePercentage = null, bool? status = null)
            {
                if (index < 1)
                {
                    index = 1;
                }
                if (size < 0 || size > 200)
                {
                    size = 20;
                }
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(DatabaseSchema.StoreSchema.Table.Discount)
                    .OrderBy("id desc");
                if (assignedTo.HasValue)
                {
                    builder.Where("[AssignedTo]=@AssignedTo", new { assignedTo });
                }
                if (usePercentage.HasValue)
                {
                    builder.Where("[UsePercentage]=@UsePercentage", new { usePercentage });
                }
                if (status.HasValue)
                {
                    builder.Where("[Status]=@Status", new { status });
                }
                builder.AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }

            internal static SqlBuilder.Template GetDiscountRequirement(int discountId)
            {
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(DatabaseSchema.StoreSchema.Table.DiscountRequirement)
                    .Where("[DiscountId]=@DiscountId", new { discountId });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            /*2012-4-5*/

            internal static SqlBuilder.Template GetAvailableDiscount()
            {
                var builder = new SqlBuilder()
                    .Field("*")
                    .Table(DatabaseSchema.StoreSchema.Table.Discount)
                    .Where("[Status]=1");
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            internal static SqlBuilder.Template GetDiscountAssignedTo(DiscountAssignedTo assignedTo, int discountId)
            {
                var builder = new SqlBuilder();
                switch (assignedTo)
                {
                    case DiscountAssignedTo.Category:
                        builder.Field("[CategoryCode]").Table(
                            DatabaseSchema.StoreSchema.Table.DiscountProductCategoryMapping);
                        break;
                    case DiscountAssignedTo.Productvariant:
                        builder.Field("[VariantId]").Table(
                            DatabaseSchema.StoreSchema.Table.DiscountProductVariantMapping);
                        break;
                }
                builder.Where("[Status]=1 AND [DiscountId]=@DiscountId", new { discountId });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            internal static SqlBuilder.Template ValidateDiscount(int discountId, long? userId)
            {
                var builder = new SqlBuilder()
                    .Field("Count(0) as Count")
                    .Table(DatabaseSchema.StoreSchema.Table.DiscountUsageHistory)
                    .Where("[DiscountId]=@DiscountId", new { discountId });
                if (userId.HasValue && userId.Value > 0)
                {
                    builder.Where("[UserId]=@UserId", new { userId });
                }
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Select);
            }

            internal static SqlBuilder.Template CreateDiscount(Discount o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.Discount)
                    .Field(
                        "([Name],[AssignedTo],[UsePercentage],[Percentage],[Amount],[StartDate],[EndDate],[Limitation],[LimitationTimes],[Status],[CreatedBy])" +
                        "VALUES(@Name,@AssignedTo,@UsePercentage,@Percentage,@Amount,@StartDate,@EndDate,@Limitation,@LimitationTimes,@Status,@CreatedBy)")
                    .AddParameters(
                        new {
                            o.Name,
                            o.AssignedTo,
                            o.UsePercentage,
                            o.Percentage,
                            o.Amount,
                            o.StartDate,
                            o.EndDate,
                            o.Limitation,
                            o.LimitationTimes,
                            o.CreatedBy
                        });

                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template CreateDiscountRequirement(DiscountRequirement o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.Discount)
                    .Field(
                        "([DiscountId],[RequirementRule],[SpentAmount],[BillingAreaCodes],[ShippingAreaCodes],[UserRoleIds],[ProductVariantIds],[CreatedBy])" +
                        "VALUES(@DiscountId,@RequirementRule,@SpentAmount,@BillingAreaCodes,@ShippingAreaCodes,@UserRoleIds,@ProductVariantIds,@CreatedBy)")
                    .AddParameters(
                        new {
                            o.DiscountId,
                            o.RequirementRule,
                            o.SpentAmount,
                            o.BillingAreaCodes,
                            o.ShippingAreaCodes,
                            o.UserRoleIds,
                            o.ProductVariantIds,
                            o.CreatedBy
                        });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            #endregion
            #region ProductAdvisory
            internal static SqlBuilder.Template CreateProductAdvisory(ProductAdvisory o)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.StoreSchema.Table.ProductAdvisory)
                .Field("([Category],[ProductId],[UserId],[NickName],[Question],[Answer],[Answerer],[AnswerTime],[Satisfaction],[DisSatisfaction],[Status],[CreatedBy],[CreatedOn])VALUES(@Category,@ProductId,@UserId,@NickName,@Question,@Answer,@Answerer,@AnswerTime,@Satisfaction,@DisSatisfaction,@Status,@CreatedBy,@CreatedOn)")
                .AddParameters(new { o.Category, o.ProductId, o.UserId, o.NickName, o.Question, o.Answer, o.Answerer, o.AnswerTime, o.Satisfaction, o.DisSatisfaction, o.Status, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template UpdateProductAdvisory(ProductAdvisory o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.ProductAdvisory)
                    .Field("[Category]=@Category", new { o.Category })
                    .Field("[ProductId]=@ProductId", new { o.ProductId })
                    .Field("[UserId]=@UserId", new { o.UserId })
                    .Field("[NickName]=@NickName", new { o.NickName })
                    .Field("[Question]=@Question", new { o.Question })
                    .Field("[Answer]=@Answer", new { o.Answer })
                    .Field("[Answerer]=@Answerer", new { o.Answerer })
                    .Field("[AnswerTime]=@AnswerTime", new { o.AnswerTime })
                    .Field("[Satisfaction]=@Satisfaction", new { o.Satisfaction })
                    .Field("[DisSatisfaction]=@DisSatisfaction", new { o.DisSatisfaction })
                    .Field("[Status]=@Status", new { o.Status })
                    .Where("[Id] = @Id", new { o.Id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template DeleteProductAdvisory(long id)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.StoreSchema.Table.ProductAdvisory)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Delete);
            }
            internal static SqlBuilder.Template GetProductAdvisory(long id)
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.StoreSchema.Table.ProductAdvisory)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }

            internal static SqlBuilder.Template GetPagedProductAdvisory(int index = 1, int size = 20, long? productId = null, int? userId = null, string createdBy = null, DateTime? createdOn = null, DateTime? answerTime = null, ProductAdvisoryCategory? category = null, ProductAdvisoryStatus? status = null)
            {
                CheckIndexSize(ref index, ref size);
                var builder = new SqlBuilder().Field("*")
                .Table(DatabaseSchema.StoreSchema.Table.ProductAdvisory)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

                if (productId.HasValue && productId.Value > 0)
                {
                    builder.Where("[ProductId=@ProductId", new { productId });
                }
                if (userId.HasValue && userId.Value > 0)
                {
                    builder.Where("[UserId=@UserId", new { userId });
                }
                if (!string.IsNullOrEmpty(createdBy))
                {
                    builder.Where("[CreatedBy=@CreatedBy", new { createdBy });
                }
                if (createdOn.HasValue)
                {
                    var createdOnBegin = new DateTime(createdOn.Value.Year, createdOn.Value.Month, createdOn.Value.Day);
                    var createdOnEnd = new DateTime(createdOn.Value.Year, createdOn.Value.Month, createdOn.Value.Day).AddDays(1);
                    builder.Where("[CreatedOn>=@CreatedOnBegin AND [CreatedOn<@CreatedOnEnd", new { createdOnBegin, createdOnEnd });
                }
                if (answerTime.HasValue)
                {
                    var answerTimeBegin = new DateTime(answerTime.Value.Year, answerTime.Value.Month, answerTime.Value.Day);
                    var answerTimeEnd = new DateTime(answerTime.Value.Year, answerTime.Value.Month, answerTime.Value.Day).AddDays(1);
                    builder.Where("[AnswerTime>=@AnswerTimeBegin AND [AnswerTime<@AnswerTimeEnd", new { answerTimeBegin, answerTimeEnd });
                }
                if (category.HasValue && category.Value > 0)
                {
                    builder.Where("[Category=@Category", new { category });
                }
                if (status.HasValue && status.Value > 0)
                {
                    builder.Where("[Status=@Status", new { status });
                }
                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }

            #endregion ProductAdvisory
            #region ProductReview
            internal static SqlBuilder.Template CreateProductReview(ProductReview o)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.StoreSchema.Table.ProductReview)
                .Field("([ProductId],[Satisfaction],[Title],[Advantage],[Shortage],[Experience],[Useful],[UserId],[NickName],[Status],[CreatedBy],[CreatedOn])VALUES(@ProductId,@Satisfaction,@Title,@Advantage,@Shortage,@Experience,@Useful,@UserId,@NickName,@Status,@CreatedBy,@CreatedOn)")
                .AddParameters(new { o.ProductId, o.Satisfaction, o.Title, o.Advantage, o.Shortage, o.Experience, o.Useful, o.UserId, o.NickName, o.Status, o.CreatedBy, o.CreatedOn });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Insert);
            }

            internal static SqlBuilder.Template UpdateProductReview(ProductReview o)
            {
                var builder = new SqlBuilder()
                    .Table(DatabaseSchema.StoreSchema.Table.ProductReview)
                    .Field("[ProductId]=@ProductId", new { o.ProductId })
                    .Field("[Satisfaction]=@Satisfaction", new { o.Satisfaction })
                    .Field("[Title]=@Title", new { o.Title })
                    .Field("[Advantage]=@Advantage", new { o.Advantage })
                    .Field("[Shortage]=@Shortage", new { o.Shortage })
                    .Field("[Experience]=@Experience", new { o.Experience })
                    .Field("[Useful]=@Useful", new { o.Useful })
                    .Field("[UserId]=@UserId", new { o.UserId })
                    .Field("[NickName]=@NickName", new { o.NickName })
                    .Field("[Status]=@Status", new { o.Status })
                    .Where("[Id] = @Id", new { o.Id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Update);
            }

            internal static SqlBuilder.Template DeleteProductReview(long id)
            {
                var builder = new SqlBuilder()
                .Table(DatabaseSchema.StoreSchema.Table.ProductReview)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.Delete);
            }

            internal static SqlBuilder.Template GetProductReview(long id)
            {
                var builder = new SqlBuilder()
                .Field("*")
                .Table(DatabaseSchema.StoreSchema.Table.ProductReview)
                .Where("[Id] = @Id", new { id });
                return builder.AddTemplate(BasicSingleTableSqlTemplate.SelectTopOne);
            }


            internal static SqlBuilder.Template GetPagedProductReview(int index = 1, int size = 20, long? productId = null,
                                                          int? userId = null, string createdBy = null,
                                                          DateTime? createdOn = null,
                                                          ProductReviewStatus? status = null)
            {
                CheckIndexSize(ref index, ref size);
                var builder = new SqlBuilder().Field("*")
                    .Table(DatabaseSchema.StoreSchema.Table.ProductReview)
                    .OrderBy("id desc")
                    .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
                /*
                .Where("");
                */
                return builder.AddTemplate(BasicSingleTableSqlTemplate.PagedSelect);
            }

            #endregion ProductReview
        }
    }
}

